import pymysql as MySQLdb
import os
from dotenv import load_dotenv

load_dotenv(dotenv_path='.flaskenv', override=True)

host = os.getenv('MYSQL_HOST')
user = os.getenv('MYSQL_USERNAME')
passwd = os.getenv('MYSQL_PASSWORD')
port = int(os.getenv('MYSQL_PORT'))
db = os.getenv('MYSQL_DATABASE')


class SelectMySQL(object):
    def select_data(self, sql):
        result = []
        try:
            conn = MySQLdb.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8mb4')
            cur = conn.cursor()
            cur.execute(sql)
            alldata = cur.fetchall()
            for rec in alldata:
                result.append(rec)
        except Exception as e:
            print("=======出错啦=======")
            print(e)
        finally:
            cur.close()
            conn.close()

        return result

    def get_user(self, sql, filename, user_id):
        results = self.select_data(sql)
        with open(filename, 'w') as f:
            for result in results:
                result = list(result)
                for index in range(len(result)):  # 开始循环
                    if result[index] is None:
                        result[index] = 'NULL'
                    elif index != 0 and index != 5 and index != 9 and index != 10:
                        result[index] = '"' + str(result[index]) + '"'
                    else:
                        result[index] = str(result[index])
                srt = 'INSERT INTO admin_user(id,username,password_hash,create_at,update_at,enable,realname,remark,avatar,dept_id,hospital_no) values (' + str(result[0]) + ',' + str(result[1]) + ',' + str(result[2]) + ',' + str(result[3]) + ',' + str(result[4]) + ',' + str(result[5]) + ',' + str(result[6]) + ',' + str(result[7]) + ',' + str(result[8]) + ',' + str(result[9]) + ',' + str(result[10]) + ');'
                f.write(srt + '\n')
            ll = 'INSERT INTO admin_user_role(user_id,role_id) VALUES (' + str(user_id) + ', 3);'
            f.write(ll + '\n')
        return results

    def get_hospital(self, sql, filename, hospital_no):
        results = self.select_data(sql)
        with open(filename, 'a') as f:
            for result in results:
                result = list(result)
                for index in range(len(result)):  # 开始循环
                    if result[index] is None:
                        result[index] = 'NULL'
                    elif index != 0 and index != 2 and index != 4:
                        result[index] = '"' + str(result[index]) + '"'
                    else:
                        result[index] = str(result[index])
                srt = 'INSERT INTO admin_hospital(id,name,user_id,notes,enable,create_time,hospital_no) values (' + result[0] + ',' + result[1] + ',' + result[2] + ',' + result[3] + ',' + result[4] + ',' + result[5] + ',' + result[6] + ');'
                f.write(srt + '\n')
        return results
